package edu.zut.cs.software.example.market.dao;

import java.util.List;

import org.springframework.data.repository.query.Param;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import edu.zut.cs.software.example.dao.GenericDao;
import edu.zut.cs.software.example.market.domain.Product;




@Repository
public interface ProductDao extends GenericDao<Product, Long>{
	@Query(value="  SELECT * FROM example.product LIMIT ?1,?2",nativeQuery = true)
	public  List<Product> getByProducts( @Param("pagenum") int pagenum,@Param("pageSize") int pageSize) ;
	
	@Query(value = "  SELECT count(*) FROM `example`.`product`  ", nativeQuery = true)
	public int getProductCounts();
	
	@Query(value = "  SELECT count(*) FROM `example`.`product` WHERE `name` LIKE CONCAT('%',?1,'%') ", nativeQuery = true)
	public int getProductByNameCounts(@Param("name") String name);
	
	@Query(value = "  SELECT count(*) FROM `example`.`product` WHERE `studentid` = ?1 ", nativeQuery = true)
	public int getProductByStudentIdCounts(@Param("studentid") Long studentid);
	
	@Query(value = "  SELECT count(*) FROM `example`.`product` WHERE `sort` LIKE CONCAT('%',?1,'%') ", nativeQuery = true)
	public int getProductBySortCounts(@Param("sort") String sort);
	
	@Query(value = "  SELECT * FROM `example`.`product` WHERE `name` LIKE CONCAT('%',?1,'%')  LIMIT ?2,?3 ", nativeQuery = true)
	public List<Product> findByName(@Param("name")String name,@Param("pagenum") int pagenum,@Param("pageSize") int pageSize);
	
	@Query(value = "  SELECT * FROM `example`.`product` WHERE `sort` LIKE CONCAT('%',?1,'%')  LIMIT ?2,?3 ", nativeQuery = true)
	public List<Product> findBySort(@Param("sort")String sort,@Param("pagenum") int pagenum,@Param("pageSize") int pageSize);
	
	@Query(value = "  SELECT * FROM `example`.`product` WHERE `studentid`= ?1 LIMIT ?2,?3 ", nativeQuery = true)
	public List<Product> findByStudentId(@Param("studentid")Long studentid,@Param("pagenum") int pagenum,@Param("pageSize") int pageSize);
	
	public boolean existsByName(String name);
	
	/**
	 * Get Student by passing an int argument
	 * @param id
	 * @return
	 */
	@Query(value = " SELECT * FROM `example`.`product` WHERE `ID`=?1 ", nativeQuery = true)
	public Product getUpdateProduct(@Param("ID") int id);

	@Modifying
	@Transactional
	@Query(value = "  DELETE FROM `example`.`product` WHERE  `ID`= ?1 ", nativeQuery = true)
	public int deleteProduct(@Param("id")Long id);

	@Query(value = " UPDATE `example`.`product` SET name=:name , price=:price , poster=:poster , description=:description , sort=:sort , studentid=:studentid WHERE ID=:id ", nativeQuery = true)
	@Modifying//
	@Transactional//事务注解
	public int editProduct(@Param("name")String name,@Param("price")String price,@Param("poster")String poster,@Param("description")String description,@Param("sort")String sort,@Param("studentid")Long studentid,@Param("id")Long id);

	
}


